﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetShowClass_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetShowClass_V2];
GO
CREATE PROCEDURE [dbo].[sproc_GetShowClass_V2]
    @UserName nvarchar(255),
    @RIGHTCode int = 1,
    @IncludeFunctionNode int = 1 
/*

============================================================
功能:    得到@UserName所有显示的组
参数:
    @UserName nvarchar(255)        :    用户名
    @RIGHTCode int =1        :    浏览权代号
    @IncludeFunctionNode        :    是否包含功能接点

备注:    可以优化
============================================================

*/
AS
BEGIN
SET NOCOUNT ON

DECLARE @Staff_id int;
DECLARE @Position_id int;

-- 取得用户的ID和职位ID
SELECT
        @staff_id = S.staff_id,
        @Position_id = P.Position_id
    FROM dbo.UDS_Staff S INNER JOIN dbo.UDS_Staff_In_Position P ON S.Staff_ID = P.Staff_ID
    WHERE S.staff_name = @username;
IF ( @@rowcount <> 1 OR @staff_id <= 0)
begin
    raiserror(N'The specified staff "%s" cannot be found.', 16, 1, @username);
    return -1;
end;

-- Get all the child positions
DECLARE @tmpPosition TABLE (id int NOT NULL);
INSERT INTO @tmpPosition exec dbo.sproc_GetAllChildPositionID_V2 @Position_id;


--得到间接的浏览权，即职位对其下属的项目监督权
--得到该接点的所有子接点
declare @tmpStaff table (staff_id int NOT NULL);

--加入本人
INSERT INTO @tmpStaff (staff_id) values(@staff_id);

--加入所有下级成员
INSERT INTO @tmpStaff (staff_id)
    SELECT
        S.staff_id
    FROM
        dbo.UDS_Staff S
        INNER JOIN dbo.UDS_Staff_In_Position P ON S.staff_id = P.staff_id
        INNER JOIN @tmpPosition TP ON P.position_id = TP.id;


--创建临时类表
DECLARE @tmpClass TABLE (obj_id int NOT NULL)

-- 把所有可显示的类 ID 收集起来
--act_id=1为显示权
-- 从职位中，能查看的接点
INSERT INTO @tmpClass ( obj_id )
    SELECT
        team_id
    FROM
        dbo.UDS_Assign_Rule R
    WHERE
        R.based_on = 1
        and R.act_id = @RIGHTCode
        and R.Position_id in
        (
            SELECT Position_id FROM dbo.uds_staff_in_position WHERE staff_id in ( SELECT staff_id FROM @tmpStaff )
        )

--从项目中，能查看的接点
INSERT INTO @tmpClass ( obj_id )
    SELECT
        team_id 
    FROM
        dbo.UDS_Assign_Rule R
    WHERE
        R.based_on = 2 
        and R.act_id = @RIGHTCode
        and R.team_id in
        (
            SELECT team_id FROM uds_staff_in_team WHERE staff_id in (SELECT staff_id FROM @tmpStaff)
            --是否只有成员拥有项目的权利
            -- and member_type = 1
        )


--项目角色，能查看的接点
INSERT INTO @tmpClass ( obj_id )
    SELECT
        A.team_id
    FROM
        dbo.uds_staff_in_team a,
        dbo.uds_activity b
    WHERE
        a.member_type = b.act_id
        and b.proc_id = @RIGHTCode
        and a.staff_id in (SELECT staff_id FROM @tmpStaff)

--从角色中，能查看的接点
INSERT INTO @tmpClass ( obj_id )
    SELECT
        team_id 
    FROM 
        UDS_Assign_Rule a
    WHERE
        a.role_id in (SELECT role_id FROM uds_staff_in_role WHERE staff_id = @staff_id)
        and based_on = 3
        and a.act_id = @RIGHTCode

--从个人中，能查看的接点
INSERT INTO @tmpClass ( obj_id )
    SELECT
        team_id 
    FROM 
        UDS_Assign_Rule a
    WHERE
        a.staff_id in (SELECT staff_id FROM @tmpStaff)
        and based_on = 4 
        and a.act_id =@RIGHTCode


IF EXISTS(SELECT 1 FROM @tmpClass WHERE obj_id = 0) 
BEGIN
    IF @IncludeFunctionNode = 1
        SELECT * FROM dbo.UDS_Class WHERE ClassID = ClassParentID ORDER BY classtype 
    ELSE
        SELECT * FROM dbo.UDS_Class WHERE ClassID = ClassParentID AND classtype <= 1 ORDER BY classtype /* 只包含项目和文档 */
END
ELSE
BEGIN

    --删除临时表@tmpClass中所有非根节点的节点
    DELETE T
    FROM @tmpClass T INNER JOIN dbo.UDS_Class CLS ON CLS.ClassID <> CLS.ClassParentID AND T.obj_id = CLS.ClassID;

    --是否包含功能接点
    IF @IncludeFunctionNode = 1 
    BEGIN
        --得到拥有的类信息
        SELECT *
            FROM dbo.UDS_Class
            WHERE ClassID IN (SELECT obj_id FROM  @tmpClass)
            ORDER BY ClassType;
    END
    ELSE
    BEGIN
        --得到拥有的类信息,只包含项目和文档
        SELECT *
            FROM dbo.UDS_Class
            WHERE
                ClassID in (SELECT obj_id FROM  @tmpClass)
                AND ClassType <= 1
            ORDER BY ClassType;
    END
END
    
END
GO